package com.hh.bss.project.service.impl;

import static com.hh.bss.util.ObjectUtils.isNotEmpty;

import java.util.ArrayList;
import java.util.List;

import com.hh.bss.base.BaseBusinessService;
import com.hh.bss.base.EntityDao;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.Page;
import com.hh.bss.project.dao.SearchProjectDao;
import com.hh.bss.project.entity.ContractJobCvOffer;
import com.hh.bss.project.service.PlanService;
import com.hh.bss.project.service.ProjectService;
import com.hh.bss.project.vo.query.PlanQuery;
import com.hh.bss.project.vo.query.ProjectQuery;
import com.hh.bss.util.DateHelper;


public class PlanServiceImpl extends BaseBusinessService<ContractJobCvOffer,java.lang.Integer>  implements PlanService {

	private SearchProjectDao searchProjectDao;
	/**增加setXXXX()方法,spring就可以通过autowire自动设置对象属性,请注意大小写*/
	public void setSearchProjectDaoImpl(SearchProjectDao dao) {
		this.searchProjectDao = dao;
	}
	public EntityDao getEntityDao() {
		return this.searchProjectDao;
	}

	
	public Page findPlanPage(PlanQuery query) {
		// TODO Auto-generated method stub
		//type=1 职位项目 type=2 CC项目 
		
		//客户拜访
		//客户电话
		//客户面试
		//顾问面试
		//成功人选跟进
		//人选生日
		//客户回款
		
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		//计划类型，详情，到期日期，到期时间，完成情况
		//客户拜访
		sql.append(" select '").append(ConstantDic.PLANTYPE_CV).append("' plantype,d.cv_name cvName ,d.position jobName, a.plan_dt expiredDt,a.id targetId from  customer_crm a,customer b,company c ,cv d  where a.cust_id=b.id and b.company_id=c.id and a.crm_type='").append(ConstantDic.ContactMethod_M).append("' and a.bd_account=? and a.crm_status = 0 and a.contacter_id = d.id  and a.plan_dt is not null ");
		sql.append(" union all ");
		//客户电话
		sql.append(" select '").append(ConstantDic.PLANTYPE_CP).append("' plantype,d.cv_name cvName ,d.position jobName,a.plan_dt expiredDt,a.id targetId from  customer_crm a,customer b,company c , cv d  where a.cust_id=b.id and b.company_id=c.id and a.crm_type='").append(ConstantDic.ContactMethod_C).append("' and a.bd_account=? and a.crm_status = 0 and a.contacter_id = d.id and a.plan_dt  is not null ");
		sql.append(" union all ");
		//客户面试
		sql.append(" select '").append(ConstantDic.PLANTYPE_CI).append("' plantype,f.cv_name cvName,c.name jobName,a.plan_dt expiredDt,a.id targetId from  contract_job_cv_interview a,contract_job_cv_recom b,contract_job c,customer d,company e,cv f where a.creator=? and (a.result is null or a.iv_dt is null or a.content is null ) and  a.recom_id=b.id and c.id=b.job_id and b.cv_id=f.id and c.cust_id=d.id and d.company_id=e.id and  a.plan_dt is not null ");
		//顾问面试
		sql.append(" union all ");
		sql.append(" select '").append(ConstantDic.PLANTYPE_GI).append("' plantype,b.cv_name cvName,c.name jobName,a.plan_dt expiredDt,a.id targetId from  search_project_interview a,cv b ,search_project c where a.interviewer= ? and ( a.result is null or a.iv_dt is null or a.content is null ) and  a.cv_id=b.id  and a.project_id = c.id and a.plan_dt is not null ");
		//成功人选跟进
		sql.append(" union all ");
		sql.append(" select '").append(ConstantDic.PLANTYPE_SF).append("' plantype,f.cv_name cvName,c.name jobName,a.crm_dt expiredDt, a.id targetId from  contract_job_cv_crm a,contract_job_cv_recom b,contract_job c,customer d,company e,cv f where a.creator=? and a.crm_status = 0  and  a.recom_id=b.id and c.id=b.job_id and b.cv_id=f.id and c.cust_id=d.id and d.company_id=e.id  and a.crm_dt is not null ");
		//人选上班
		sql.append(" union all ");
		sql.append(" select '").append(ConstantDic.PLANTYPE_CS).append("' plantype,d.cv_name cvName,c.name jobName,a.onduty_d expiredDt, a.id targetId from  contract_job_cv_offer a,contract_job_cv_recom b,contract_job c ,cv d where   a.recom_id=b.id and c.id=b.job_id and b.cv_id=d.id and a.creator=? and a.checkin_d is null and  a.onduty_d is not null ");
		//人选生日
		sql.append(" union all ");
		sql.append(" select '").append(ConstantDic.PLANTYPE_CB).append("' plantype,c.cv_name cvName,c.position jobName,c.birthday expiredDt,c.id targetId from  search_project a,search_project_cv b,cv c where a.account=? and a.id=b.project_id and b.cv_id=c.id and c.birthday = curdate() group by b.cv_id ");
		//PN账单
		sql.append(" union all ");		
		sql.append(" select '").append(ConstantDic.PLANTYPE_PN).append("' plantype,e.cv_name cvName,d.name jobName,ifnull(f.arDt,aa.create_dt) expiredDt,aa.id targetId from ( ");
		sql.append(" select a.id,a.create_dt,a.final_ar,a.nbr,c.cv_id,c.job_id,a.send_dt from  account_pn a,contract_job_cv_offer b,contract_job_cv_recom c where a.offer_id=b.id and b.recom_id=c.id and a.creator=? ");
		sql.append(" ) aa left join contract_job d on aa.job_id=d.id left join cv e on aa.cv_id=e.id ");
		sql.append(" left join (select pn_nbr,max(ar_dt) arDt from  account_ar group by pn_nbr) f on  f.pn_nbr=aa.nbr ");
		sql.append(" where not exists(select 1 from (select pn_nbr,sum(amount) amount from  account_ar where confirm_dt is not null group by pn_nbr) f where f.pn_nbr=aa.nbr and f.amount>=aa.final_ar  ) ");
		
		sql.append(" union all ");		
		sql.append(" select '").append(ConstantDic.PLANTYPE_CR).append("' plantype,b.cv_name cvName,c.name jobName,a.plan_dt expiredDt,a.id targetId from search_project_cv_crm a ");
		sql.append(" left  join cv b  on a.cv_id = b.id  ");
		sql.append(" left  join search_project c on a.project_id = c.id  ");
		sql.append(" where a.creator=?  and a.plan_dt is not null and (a.crm_dt is null or length(ifnull(a.comment,''))=0) ");
		
		sql.append(" ) pc ");
		sql.append(" where 1=1 ");
		//--
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		values.add(query.getOperator());
		//--
		
		//今天
		if(isNotEmpty(query.getType())&&query.getType().intValue()==1) { 		    
		    sql.append(" and ( pc.expiredDt >= ?  and  pc.expiredDt < ?)");		    
			values.add(DateHelper.getToday());
			values.add(DateHelper.add(DateHelper.getToday(), DateHelper.DAY, 1));
		}
		//明天
		if(isNotEmpty(query.getType())&&query.getType().intValue()==2) { 		    
		    sql.append(" and ( pc.expiredDt >= ?  and  pc.expiredDt < ?)");		    
		    values.add(DateHelper.add(DateHelper.getToday(), DateHelper.DAY, 1));
			values.add(DateHelper.add(DateHelper.getToday(), DateHelper.DAY, 2));
		}
		sql.append(" order by pc.expiredDt desc ");
		System.out.println(sql.toString());
		
		return getEntityDao().getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
		
		
		//return null;
	}

}
